﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace EARS.App_Code
{
    public class AccountDataClass
    {
        public string GetNameByID(string userID)
        {
            string name = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Name from Account where UserID = @UserID";
                comm.Parameters.AddWithValue("@UserID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    name = dr["Name"].ToString();
                }
                dr.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return name;
        }

        public int GetContactNoByID(string userID)
        {
            int contactNo = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select ContactNo from Account where UserID = @UserID";
                comm.Parameters.AddWithValue("@UserID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    contactNo = (int)dr["ContactNo"];
                }
                dr.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return contactNo;
        }

        public string GetEmailByID(string userID)
        {
            string email = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Email from Account where UserID = @UserID";
                comm.Parameters.AddWithValue("@UserID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    email = dr["Email"].ToString();
                }
                dr.Close();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return email;
        }

        public string ForgetPassword(string userID)
        {
            string email = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select email from account where UserID = @userID";
                comm.Parameters.AddWithValue("@userID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    email = dr["email"].ToString();
                }
                dr.Close();
                return email;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public string RetrieveName(string userID)
        {
            string name = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Name from Account where UserID = @userID";
                comm.Parameters.AddWithValue("@userID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    name = dr["Name"].ToString();
                }
                dr.Close();
                return name;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public string RetrievePassword(string userID)
        {
            string password = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select Password from Account where UserID = @userID";
                comm.Parameters.AddWithValue("@userID", userID);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    password = dr["Password"].ToString();
                }
                dr.Close();
                return password;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public void CreateAccount(Account a)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();

                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Account" + "(UserID, Password, Name, Course, Email, ContactNo, YearofStudy, MatricNo, Role) values " +
                    "(@userID, @password, @name, @course, @email, @contactno, @yearofstudy, @matricno, @role)";
                comm.Parameters.AddWithValue("@userID", a.UserID);
                comm.Parameters.AddWithValue("@password", a.Password);
                comm.Parameters.AddWithValue("@name", a.Name);
                comm.Parameters.AddWithValue("@matricno", a.MatricNo);
                comm.Parameters.AddWithValue("@email", a.Email);
                comm.Parameters.AddWithValue("@contactno", a.ContactNo);
                comm.Parameters.AddWithValue("@yearofstudy", a.YearOfStudy);
                comm.Parameters.AddWithValue("@course", a.Course);
                comm.Parameters.AddWithValue("@role", a.Role);
                int ra = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        public void ChangePassword(string userID, string newPassword)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "update Account set Password=@Password where UserID=@UserID";
                comm.Parameters.AddWithValue("@UserID", userID);
                comm.Parameters.AddWithValue("@Password", newPassword);
                int row = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public string GetMatric(string matricNo)
        {
            string num = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EarsDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select MatricNo from Account where MatricNo = @MatricNo";
                comm.Parameters.AddWithValue("@MatricNo", matricNo);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.HasRows)
                {
                    if (dr.Read())
                    {
                        num = dr["MatricNo"].ToString();
                    }
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return num;
        }
    }
}